<?php
namespace app\admin\service\common;

use think\facade\Db;
use app\admin\service\BaseService;

class TablesService extends BaseService
{
    /**
     * 数据表
     * @param $param
     * @return array
     */
    public function getList($param){
        $tables = Db::query('SHOW TABLE STATUS');
        // 搜索
        if (!empty($param['search'])) {
            $search = $param['search'];
            $tables = array_filter($tables, function($row) use ($search) {
                // 可按表名和注释模糊搜索
                return stripos($row['Name'], $search) !== false
                    || stripos($row['Comment'], $search) !== false;
            });
        }
        // 处理显示字段
        foreach ($tables as &$t) {
            $t['Data_length_mb'] = round($t['Data_length']/1024/1024, 2);
            $t['Index_length_mb'] = round($t['Index_length']/1024/1024, 2);
            $t['Data_free_mb'] = round($t['Data_free']/1024/1024, 2);
        }
        // 分页
        $tables = array_values($tables); // 重新索引
        $current = isset($param['current']) ? intval($param['current']) : 1;
        $size = isset($param['size']) ? intval($param['size']) : 10;
        $total = count($tables);
        $list = array_slice($tables, ($current-1)*$size, $size);
        $last_page = ceil($total / $size);
        $data = [
            'total' => $total,
            'per_page' => $size,
            'current_page' => $current,
            'last_page' => $last_page,
            'data' => $list,
        ];
        return success($data,'成功');
    }
    //执行SQL语句
    public function tableSQL($param){
        $debug = env('APP_DEBUG');
        $TOKEN = getJWT(getHeaderToken());
        $USERID = $TOKEN['id'];
        if(!$debug ){
            return fail('仅开发模式下操作');
        }
        if($USERID !== 1){
            return fail('仅超级管理员可操作');
        }
        if (empty($param['sql'])) {
            return fail('sql语句不可以为空');
        }
        Db::startTrans();
        try{
            $result = Db::execute($param['sql']);
            Db::commit();
            $this->writeLog('执行SQL语句', 1);
            return success($result,'操作成功');
        } catch (\Exception $e) {
            Db::rollback();
            return fail('操作失败:' . $e->getMessage());
        }
        
    }
    //删除/清理表数据
    public function batch($ids,$type){
        $debug = env('APP_DEBUG');
        $TOKEN = getJWT(getHeaderToken());
        $USERID = $TOKEN['id'];
        if(!$debug ){
            return fail('仅开发模式下操作');
        }
        if($USERID !== 1){
            return fail('仅超级管理员可操作');
        }
        // 验证参数
        if (empty($type)) {
            return fail('未指定操作类型');
        }
        $ids = array_filter(array_unique($ids));
        if (empty($ids)) {
            return fail('请选择要删除的数据');
        }
        try {
            switch ($type) {
                case '1': // 优化表
                    foreach ($ids as $table) {
                        Db::execute("OPTIMIZE TABLE `{$table}`");
                        Db::execute("REPAIR TABLE `{$table}`");
                        Db::execute("TRUNCATE TABLE `{$table}`");
                        $this->writeLog('清空表操作: ' . $table, 3); // 记录日志
                    }
                    return success('清空表成功');
                case '2': // 删除表
                    foreach ($ids as $table) {
                        Db::execute("DROP TABLE `{$table}`");
                        $this->writeLog('删除数据表: ' . $table, 3); // 记录日志
                    }
                    return success('表删除成功');

                default:
                    return fail('未知操作');
            }
        } catch (\Exception $e) {
            return fail('操作失败: ' . $e->getMessage());
        }
    }
    //备份
    public function backup($param){
        $debug = env('APP_DEBUG');
        $TOKEN = getJWT(getHeaderToken());
        $USERID = $TOKEN['id'];
        if(!$debug ){
            return fail('仅开发模式下操作');
        }
        if($USERID !== 1){
            return fail('仅超级管理员可操作');
        }
        try {
            $config = config('database.connections.mysql');
            $dbName = $config['database'];
            $tables = Db::query('SHOW TABLES');
            $sql = "SET NAMES utf8mb4;\n";
            $sql .= "SET foreign_key_checks = 0;\n\n";
            foreach ($tables as $table) {
                $tableName = array_values($table)[0];
                $createResult = Db::query("SHOW CREATE TABLE `{$tableName}`");
                if (!empty($createResult)) {
                    $sql .= "DROP TABLE IF EXISTS `{$tableName}`;\n";
                    $sql .= $createResult[0]['Create Table'] . ";\n\n";
                }
                $countResult = Db::query("SELECT COUNT(*) as total FROM `{$tableName}`");
                $totalRows = $countResult[0]['total'] ?? 0;
                if ($totalRows > 0 && $totalRows <= 5000) {
                    $fields = Db::query("SHOW COLUMNS FROM `{$tableName}`");
                    $fieldNames = array_column($fields, 'Field');
                    $data = Db::query("SELECT * FROM `{$tableName}` LIMIT 5000");
                    if (!empty($data)) {
                        foreach ($data as $row) {
                            $values = [];
                            foreach ($fieldNames as $field) {
                                $value = $row[$field] ?? null;
                                if ($value === null) {
                                    $values[] = 'NULL';
                                } else {
                                    $values[] = "'" . addslashes($value) . "'";
                                }
                            }
                            $sql .= "INSERT INTO `{$tableName}` (`" . implode('`, `', $fieldNames) . "`) VALUES (" . implode(', ', $values) . ");\n";
                        }
                        $sql .= "\n";
                    }
                }
            }
            $sql .= "SET foreign_key_checks = 1;\n";
            $fileName = $dbName . '_' . date('Ymd_His') . '.sql';
            $this->writeLog('备份完成: ' . count($tables) . ' tables', 4);
            return [
                'sql' => $sql,
                'fileName' => $fileName
            ];
            
        } catch (\Exception $e) {
            return alert(500,'Backup failed: '. $e->getMessage());
        }
    }
}